// 使用CommonJS语法
const dayjs = require('dayjs');
const util = require("../../common/util");
// 创建 MySQL 连接池
const pool = require("../../common/util");

class ShelfItemDB {
    constructor() {
        this.instance = null;
        this.pool = pool;
    }

    /**
     * 获取ShelfItemDB的单例实例
     * @returns {ShelfItemDB} ShelfItemDB的实例
     */
    static getInstance() {
        if (!this.instance) {
            this.instance = new ShelfItemDB();
        }
        return this.instance;
    }
}

/**
 * 入库操作
 * @param {Object} shelfItem - 包含货架物品信息的对象
 * @returns {Promise<number>} 受影响的行数
 */
ShelfItemDB.prototype.add = async function (shelfItem) {
    try {

        const sql = 'INSERT INTO shelf_items (shelf_id, layer_number, item_id, delivery_id) VALUES (?, ?, ?)';
        const params = [shelfItem.shelfId, shelfItem.layerNumber, shelfItem.itemId, shelfItem.deliveryId];
        console.log(shelfItem.itemId);
        const result = await this.pool.query(sql, params);
        return result.affectedRows;
    } catch (error) {
        util.err('Error adding shelf item information:', error);
        throw error;
    }
}

/**
 * 出库操作
 * 根据ID更新shelf_items表中的货架物品记录，表示物品已出库
 * @param {number} id - 货架物品的ID
 * @returns {Promise<Object>} 更新操作的结果
 */
ShelfItemDB.prototype.outbound = async function (id) {
    try {
        // 构造更新预约的 SQL 语句
        const sql = 'UPDATE shelf_items SET status = "已取出" WHERE id = ?';
        const params = [id];
        const result = await this.pool.query(sql, params);
        return result.affectedRows;
    } catch (error) {
        util.err('Error updating shelf item for outbound:', error);
        throw error;
    }
}

/**
 * 更新shelf_items表中的货架物品记录
 * @param {Object} shelfItem - 包含更新信息的货架物品对象
 * @returns {Promise<Object>} 更新操作的结果
 */
ShelfItemDB.prototype.update = async function (shelfItem) {
    try {
        const sql = 'UPDATE shelf_items SET shelf_id = ?, layer_number = ?, item_id = ?, status = ? WHERE id = ?';
        const params = [shelfItem.shelfId, shelfItem.layerNumber, shelfItem.itemId, shelfItem.status, shelfItem.id];
        const result = await this.pool.query(sql, params);
        return result;
    } catch (error) {
        util.err('Error updating shelf item information:', error);
        throw error;
    }
}

/**
* 根据ID查找shelf_items表中的特定货架物品记录
* @param {number} id - 货架物品的ID
* @returns {Promise<Object>} 货架物品记录
*/
ShelfItemDB.prototype.find = async function (id) {
    try {
        // 在SQL查询中，直接根据status枚举值来判断是否出库，这里假设'已取出'代表已出库，其他状态视为未出库
        const sql = `
          SELECT
            shelf_items.id,
            I.name AS 商品名称,
            U1.nickname AS 收件人姓名,
            shelf_items.retrieve_code AS 取件码,
            U2.nickname AS 入库人姓名,
            shelf_items.created_at AS 入库时间,
            I.retrieve_time AS 出库时间,
            CASE 
                WHEN shelf_items.status = '已取出' THEN true 
                ELSE false 
            END AS 是否出库
          FROM
            shelf_items
          JOIN
            Items I ON shelf_items.item_id = I.id
          JOIN
            Users U1 ON I.owner_id = U1.id
          LEFT JOIN
            Users U2 ON shelf_items.delivery_id = U2.id 
          WHERE
            shelf_items.id = ?; 
          `;
        const params = [id];
        const result = await this.pool.query(sql, params);
        if (!result || result.length === 0) {
            // 没有找到对应记录，根据应用逻辑决定是否抛出错误或返回特定信息
            return null; // 或者根据需要处理
        }
        return result[0]; // 返回查询结果的第一条记录
    } catch (error) {
        util.err('Error finding shelf item information:', error);
        throw error;
    }
}

/**
 * 查找shelf_items表中的所有货架物品记录
 * @returns {Promise<Array>} 所有货架物品记录的数组
 */

ShelfItemDB.prototype.findAll = async function () {
    try {
        // 在SQL查询中，直接根据status枚举值来判断是否出库，这里假设'已取出'代表已出库，其他状态视为未出库
        const sql = `
          SELECT
            shelf_items.id,
            I.name AS 商品名称,
            U1.nickname AS 收件人姓名,
            shelf_items.retrieve_code AS 取件码,
            U2.nickname AS 入库人姓名,
            shelf_items.created_at AS 入库时间,
            I.retrieve_time AS 出库时间,
            CASE 
                WHEN shelf_items.status = '已取出' THEN true 
                ELSE false 
            END AS 是否出库
          FROM
            shelf_items
          JOIN
            Items I ON shelf_items.item_id = I.id
          JOIN
            Users U1 ON I.owner_id = U1.id
          LEFT JOIN
            Users U2 ON shelf_items.delivery_id = U2.id; 
          `;
        const params = [];
        const result = await this.pool.query(sql, params);
        if (!result || result.length === 0) {
            // 没有找到对应记录，根据应用逻辑决定是否抛出错误或返回特定信息
            return null; // 或者根据需要处理
        }
        return result; // 返回查询结果的第一条记录
    } catch (error) {
        util.err('Error finding shelf item information:', error);
        throw error;
    }
}


/**
 * 查找shelf_items表中的所有货架物品记录
 * @param {string} status - 物品状态，用于模糊查询
 * @param {number} limit - 查询结果数量限制
 * @param {number} offset - 分页偏移
 * @param {string} orderBy - 排序字段，如'created_at'、'retrieve_time'等
 * @returns {Promise<Array>} 查询结果的数组
 */
ShelfItemDB.prototype.searchByStatus = async function (status, limit = -1, offset = -1, orderBy = 'created_at DESC') { // 添加默认排序字段  
    try {  
        // 构造查询语句  
        const sql = `  
          SELECT  
            shelf_items.id,  
            I.name AS 商品名称,  
            U1.nickname AS 收件人姓名,  
            shelf_items.retrieve_code AS 取件码,  
            U2.nickname AS 入库人姓名,  
            shelf_items.created_at AS 入库时间,  
            I.retrieve_time AS 出库时间,  
            CASE   
                WHEN shelf_items.status = '已取出' THEN true   
                ELSE false   
            END AS 是否出库  
          FROM  
            shelf_items  
          JOIN  
            Items I ON shelf_items.item_id = I.id  
          JOIN  
            Users U1 ON I.owner_id = U1.id  
          LEFT JOIN  
            Users U2 ON shelf_items.delivery_id = U2.id  
          WHERE  
            shelf_items.status LIKE ?  
          ORDER BY  
            ${orderBy}  
          ${limit !== -1 || offset !== -1 ? 'LIMIT ? OFFSET ?' : ''}  
        `;  
        // 参数数组  
        const params = [`%${status}%`];  
        // 添加limit和offset参数  
        if (limit !== -1 && offset !== -1) {  
            params.push(limit);  
            params.push(offset);  
        }  
        // 使用参数化查询  
        const result = await this.pool.query(sql, params); // 注意这里通常返回一个包含rows和fields的数组  
        if (result.length === 0) {  
            // 没有找到对应记录，返回空数组  
            return [];  
        }  
        console.log(result);
        return result; // 返回所有查询结果  
    } catch (error) {  
        util.err('Error finding shelf item information:', error);  
        throw error;  
    }  
}

/**
 * 查找shelf_items表中的用户自己货架物品列表记录
 * @param {string} status - 物品状态，用于模糊查询
 * @param {number} limit - 查询结果数量限制
 * @param {number} offset - 分页偏移
 * @param {string} orderBy - 排序字段，如'created_at'、'retrieve_time'等
 * @returns {Promise<Array>} 查询结果的数组
 */
ShelfItemDB.prototype.searchSelfByStatus = async function (status, limit = -1, offset = -1, orderBy = 'created_at DESC', id) { // 添加默认排序字段  
    try {  
        // 构造查询语句  
        const sql = `  
          SELECT  
            shelf_items.id,  
            I.name AS 商品名称,  
            U1.nickname AS 收件人姓名,  
            shelf_items.retrieve_code AS 取件码,  
            U2.nickname AS 入库人姓名,  
            shelf_items.created_at AS 入库时间,  
            I.retrieve_time AS 出库时间,  
            CASE   
                WHEN shelf_items.status = '已取出' THEN true   
                ELSE false   
            END AS 是否出库  
          FROM  
            shelf_items  
          JOIN  
            Items I ON shelf_items.item_id = I.id  
          JOIN  
            Users U1 ON I.owner_id = U1.id  
          LEFT JOIN  
            Users U2 ON shelf_items.delivery_id = U2.id  
          WHERE  
            shelf_items.status LIKE ? AND I.owner_id = ?
          ORDER BY  
            ${orderBy}  
          ${limit !== -1 || offset !== -1 ? 'LIMIT ? OFFSET ?' : ''}  
        `;  
        // 参数数组  
        const params = [`%${status}%`,id];  
        // 添加limit和offset参数  
        if (limit !== -1 && offset !== -1) {  
            params.push(limit);  
            params.push(offset);  
        }  
        // 使用参数化查询  
        const result = await this.pool.query(sql, params); // 注意这里通常返回一个包含rows和fields的数组  
        if (result.length === 0) {  
            // 没有找到对应记录，返回空数组  
            return [];  
        }  
        console.log(result);
        return result; // 返回所有查询结果  
    } catch (error) {  
        util.err('Error finding shelf item information:', error);  
        throw error;  
    }  
}
/**
 * 获取shelf_items表中的记录总数
 * @returns {Promise<Object>} 包含总数的结果对象
 */
ShelfItemDB.prototype.getCount = async function () {
    try {
        const sql = "SELECT COUNT(*) as total FROM shelf_items";
        const result = await this.pool.query(sql);
        return result;
    } catch (err) {
        throw new Error(`Error getting shelf items count: ${err.message}`);
    }
}

/**
 * 出库操作
 * 根据ID删除shelf_items表中的特定货架物品记录
 * @param {number} id - 货架物品的ID
 * @returns {Promise<Object>} 删除操作的结果
 */
ShelfItemDB.prototype.remove = async function (id) {
    try {
        const sql = 'DELETE FROM shelf_items WHERE id = ?';
        const params = [id];
        const result = await this.pool.query(sql, params);
        return result;
    } catch (error) {
        util.err('Error removing shelf item information:', error);
        throw error;
    }
}


/**
 * 获取过期的物品记录
 * @param {string} val - 时间段标识，例如'1'代表超时一周，'2'代表超时三周等
 * @returns {Promise<Array>} 过期物品的数组
 */
ShelfItemDB.prototype.getExpiredItems = async function (val) {
    let arr = [];
    let timeWhere = '';
    let currentTime = dayjs().format('YYYY-MM-DD HH:mm:ss');

    switch (val) {
        case '1': // 超时一周
            timeWhere = `shelf_items.created_at < '${dayjs().subtract(7, 'day').format('YYYY-MM-DD HH:mm:ss')}'`;
            break;
        case '2': // 超时三周
            timeWhere = `shelf_items.created_at < '${dayjs().subtract(21, 'day').format('YYYY-MM-DD HH:mm:ss')}'`;
            break;
        default:
            throw new Error("Invalid expiration period identifier.");
    }

    try {
        // 确保在WHERE条件中同时检查status和时间
        let query = `
        SELECT 
            shelf_items.*, 
            Items.name AS item_name, 
            u1.nickname AS owner_nickname,
            u2.nickname AS delivery_nickname 
        FROM 
            shelf_items
        INNER JOIN Items ON shelf_items.item_id = Items.id
        INNER JOIN Users u1 ON Items.owner_id = u1.id
        LEFT JOIN Users u2 ON shelf_items.delivery_id = u2.id
        WHERE 
            shelf_items.status = '在库存' AND 
            ${timeWhere}`;
        params = []
        console.log(query); // 打印查询语句，便于检查
        let sgList = await this.pool.query(query, params);

        // 确认真处理结果，确保返回数据符合预期
        arr = sgList.map(item => ({
            ...item, 
            created_at: dayjs(item.created_at).format('YYYY-MM-DD HH:mm:ss'),
            delivery_nickname: item.delivery_nickname || '未指定快递员' // 如果没有快递员信息，则使用默认值
        }));

        return arr;
    } catch (error) {
        util.err('Error fetching expired items:', error);
        throw error;
    }
}
module.exports = ShelfItemDB;
